package com.sk.mybatis.mapper;

import com.sk.mybatis.model.UserInfo;
import org.apache.ibatis.annotations.*;

import java.util.List;
@Mapper
public interface UserInfoMapper {
//    @ResultMap("resultMap")
    @Select("select * from userinfo")
    List<UserInfo> getUserInfoAll();

    @Select("select * from userinfo where delete_flag = #{deleteFlag}")
        //此处因为只有一个参数,所以名称对不上也没有关系
    List<UserInfo> getUserInfoByFlag(Integer Flag);

    @Select("select * from userinfo where delete_flag = #{deleteFlag} and gender = #{gender}")
    List<UserInfo> getUserInfo2(Integer deleteFlag, Integer gender);

//    @Select("select * from userinfo where delete_flag = #{deleteFlag} and gender = #{gender}")
//    List<UserInfo> getUserInfo3(Integer flag, Integer gender);//会报错,绑定异常,没有匹配到delete_Flag

    @Select("select * from userinfo where delete_flag = #{param1} and gender = #{gender}")
        //可以使用param+数字来代替,表示第几个参数此时也能正常运行
    List<UserInfo> getUserInfo3(Integer flag, Integer gender);

    @Select("select * from userinfo where delete_flag = #{deleteFlag} and gender = #{gender}")
        //加上Param注解后,sql中使用的的参数必须和@Param参数保持一致,参数绑定
    List<UserInfo> getUserInfo4(@Param("deleteFlag") Integer flag, Integer gender);

    @Select("select * from userinfo")
        //会报错,期待只有一个结果但是返回多个
        //如果只有个对象返回,可以使用对象或者List接受,但如果有多个结果返回,就必须使用List来接受
    UserInfo getUserInfo();

    /**
     * 增
     */
    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into userinfo (username,password,age,gender) values (#{username},#{password},#{age},#{gender})")
    Integer insert(UserInfo userInfo);

    //参数绑定
    @Insert("insert into userinfo (username,password,age,gender) " +
            "values (#{userInfo.username},#{userInfo.password},#{userInfo.age},#{userInfo.gender})")
    Integer insert2(@Param("userInfo") UserInfo userInfo);
    /**
     * 删
     */
    @Delete("delete from userinfo where id = #{id}")
    Integer delete(Integer id);
    /**
     * 更新
     */
    @Update("update userinfo set password = #{password}, age = #{age}, gender = #{gender} where id = #{id}")
    Integer update(UserInfo userInfo);
    /**
     * 查询
     */
    /*
    1. 起别名
     */
    @Select("select id, username, password, age, gender, phone, " +
            "delete_flag as deleteFlag, create_time as createTime, update_time as updateTime " +
            "from userinfo")
    List<UserInfo> selectUserInfos();

    /*
    2. 结果映射
     */
    @Results(id = "resultMap",value = {
            @Result(column = "delete_flag",property = "deleteFlag"),
            @Result(column = "create_time",property = "createTime"),
            @Result(column = "update_time",property = "updateTime")

    })
    @Select("select * from userinfo")
    List<UserInfo> selectUserInfos2();

    /*
    3. 驼峰自动转换
     */
    //在配置文件

    /**
     * #{}和${}区别(提前占位和即时拼接）
     * #是预编译sql：1.性能更高 2.更安全(防止sql注入)，但它有时会遇到不需要参数加引号的情况，比如排序、模糊匹配(模糊匹配建议使用conact函数拼接）
     * $是即时sql(有些情况仍然适用，比如排序order by id desc(此时desc不能加引号),但仍不推荐，可以使用枚举或者多定义几个接口
     * 能使用#完成的都不使用$
     */
    @Select("select * from userinfo where username = #{userName}")
    UserInfo getUserInfoByName(String userName);

    @Select("select * from userinfo where username = ${userName}")//这里运行测试方法报错了
    UserInfo getUserInfoByName2(String userName);
    /*
    *演示sql注入
     */
    @Select("select * from userinfo where username = #{userName}")
    List<UserInfo> selectUserByName(String userName);

    @Select("select * from userinfo where username = '${userName}'")
    List<UserInfo> selectUserByName2(String userName);

    @Select("select * from userinfo where username = '${username}' and password = '${password}'")
    UserInfo queryUserByNameAndPassword(String username, String password);
}
